package picasa;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class databaseConnection {
    private static  Connection conn;
	
	public databaseConnection(){
		try{
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/gicasa", "root", "ja2241");
			databaseConnection.conn = conn;
		}catch(Exception e){
			System.out.println("Error in creating connection: " + e);
		}//end of catch
	}
	
	public void addUser(String userid, String password, String fname, String lname, String street, String city, String zipcode,String email){
		try{
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("Insert into userinfo Value('" + userid + "', '" + fname + "', '" + lname + "', '" + street + "', '" + city + "', '" + zipcode + "', '" + email + "')");
			stmt.executeUpdate("Insert into useraccount Value('" + userid + "', '" + password + "', false)");
			stmt.close();
		} catch (Exception e){
			System.out.println("Error in addUser: " + e.getMessage());
		}
		
	}//end of addUser method
	
	public void addPic(String userid, String foldername, String picname){
		try{
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("Insert into folderpics Value('" + userid + "', '" + foldername + "', '" + picname + "')");
			stmt.close();
		} catch (Exception e){
			System.out.println("Error in addPicture: " + e.getMessage());
		}
	}
	
	public boolean checkUser(String userid, String password){
		boolean valid = false;
		try{
			Statement stmt = conn.createStatement();
			String qry = "Select userid, password, loggedin from useraccount where userid ='" + userid + "' and password = '" + password + "'";
			ResultSet rset = stmt.executeQuery(qry);
			if(rset.next()){
				if(userid.equals(rset.getString(1))) {
					if(password.equals(rset.getString(2))){
						if(rset.getString(3).equals("0")){
							valid = true;
						}
					}
				}
			}
		}catch(Exception e){
			System.out.println("Error in checkUser:" + e.getMessage());
		}
		return valid;
	}//end of checkUser method
	public boolean checkId(String userid){
		boolean valid = true;
		try{
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery("Select userid from useraccount where userid = '" + userid + "'");
			if(rset.next()){
				if(userid.equals(rset.getString(1))){
					valid = false;
				}
			}
		}catch(Exception e){
			System.out.println("Error in checkId:" + e.getMessage());
		}
		return valid;
	}
	public List getFolders(String userid){
		ArrayList folders = new ArrayList();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery("Select foldername from userfolders where userid='" + userid + "'");
			rset.next();
			do{
				folders.add(rset.getString(1));
			}while(rset.next());
			stmt.close();
		} catch (Exception e){
			System.out.println("Error in getFolders: " + e.getMessage());
		}
		return folders;
	}
	public List getNumbers(String userid){
		ArrayList numbers = new ArrayList();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery("Select numberofpics from userfolders where userid='" + userid + "'");
			rset.next();
			do{
				numbers.add(rset.getString(1));
			}while(rset.next());
			stmt.close();
		} catch (Exception e){
			System.out.println("Error in getNumbers: " + e.getMessage());
		}
		return numbers;
	}
	public void UpdateNumber(String userid,String foldername){
		try{
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery("Select numberofpics from userfolders where userid='" + userid + "' and foldername='" + foldername + "'");
			rset.next();
			int number = Integer.parseInt(rset.getString(1));
			number +=1;
			stmt.executeUpdate("Update userfolders Set numberofpics = " + number + " where userid='" + userid + "' and foldername='" + foldername + "'" );
			stmt.close();
		} catch (Exception e){
			System.out.println("Error in addPicture: " + e.getMessage());
		}
		
	}
	public StringBuffer getPhotos(String userid){
		StringBuffer results = new StringBuffer("<folders>");
		String tempFolder = "";
		try{
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery("Select userfolders.foldername,numberofpics,location,picname from userfolders inner join folderpics on userfolders.userid = folderpics.userid and userfolders.foldername = folderpics.foldername where userfolders.userid ='" + userid + "' Order by folderpics.foldername");
			rset.next();
			results.append("<folder>");
			results.append("<name>");
			results.append(rset.getString(1));
			results.append("</name>");
			results.append("<number>");
			results.append(rset.getString(2));
			results.append("</number>");
			results.append("<location>");
			results.append(rset.getString(3));
			results.append("</location>");
			tempFolder= rset.getString(1);
			do{
				if(rset.getString(1).equals(tempFolder)){
					//adds the pictures to the same folder
					results.append("<picname>");
				    results.append(rset.getString(4));
				    results.append("</picname>");
				}else{
					//sets up a new folder for pictures
					results.append("</folder>");
					results.append("<folder>");
					tempFolder = rset.getString(1);
					results.append("<name>");
					results.append(rset.getString(1));
					results.append("</name>");
					results.append("<number>");
					results.append(rset.getString(2));
					results.append("</number>");
					results.append("<location>");
					results.append(rset.getString(3));
					results.append("</location>");
					results.append("<picname>");
					results.append(rset.getString(4));
					results.append("</picname>");
				}
				    
			}while(rset.next());
			results.append("</folder>");
			results.append("</folders>");
			stmt.close();
		} catch (Exception e){
			System.out.println("Error in getPhotos: " + e.getMessage());
		}
		return results;
	}
	public void login(String userid){
		try{
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("Update useraccount set loggedin= true where userid = '" + userid + "'");
		}catch(Exception e){
			System.out.println("Error in login:" + e.getMessage());
		}
		
	}
	public void logout(String userid){
		try{
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("Update useraccount set loggedin= false where userid = '" + userid + "'");
		}catch(Exception e){
			System.out.println("Error in logout:" + e.getMessage());
		}
	}
}//end of public class

